<?php

class Quanly_m extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->database();
    }

    function insert_data($table, $data) {
        $this->db->insert($table, $data);
        return $this->db->affected_rows() ? true : false;
    }

    function update_data($table, $data, $id) {
        $this->db->update($table, $data, array('id' => $id));
        return $this->db->affected_rows() ? true : false;
    }

    function delete_data($table, $id) {
        $this->db->delete($table, array('id' => $id));
        return $this->db->affected_rows() ? true : false;
    }

    function delete_pay_period($id) {
        $this->db->delete('dot_thanh_toan', array('id_dot_thanh_toan' => $id));
        return $this->db->affected_rows() ? true : false;
    }

    function _count_data($table) {
        $query = $this->db->select()
                ->from($table)
                ->get();
        return $this->db->num_rows();
    }

    function _get_data_by_id($table, $id) {
        $query = $this->db->select()
                ->from($table)
                ->where('id', $id)
                ->limit(1)
                ->get();
        return $query->row();
    }

    function _get_customer_by_sale_id($sale_id, $perpage = 0, $offset = 0, $count = FALSE) {
        if ($count === TRUE) {
            $query = $this->db->select('khachhang.*')
                    ->from('khachhang')
                    ->where('khachhang.sale_id', $sale_id)
                    ->get();
            return $query->num_rows();
        } else {
            $query = $this->db->select('khachhang.*')
                    ->from('khachhang')
                    ->where('khachhang.sale_id', $sale_id)
                    ->limit($perpage, $offset)
                    ->get();
            return $query->result_array();
        }
    }

    function _get_customers($perpage = 0, $offset = 0, $count = FALSE) {
        if ($count === TRUE) {
            $query = $this->db->select('khachhang.*')
                    ->from('khachhang')
                    ->get();
            return $query->num_rows();
        } else {
            $query = $this->db->select('khachhang.*')
                    ->from('khachhang')
                    ->limit($perpage, $offset)
                    ->get();
            return $query->result_array();
        }
    }

    function _get_contact_by_sale_id($sale_id, $perpage = 0, $offset = 0, $count = FALSE) {
        if ($count === TRUE) {
            $query = $this->db->select()
                    ->from('lienhe')
                    ->where('lienhe.id_sale', $sale_id)
                    ->get();
            return $query->num_rows();
        } else {
            $query = $this->db->select()
                    ->from('lienhe')
                    ->where('lienhe.id_sale', $sale_id)
                    ->limit($perpage, $offset)
                    ->get();
            return $query->result_array();
        }
    }

    function _get_contacts($perpage = 0, $offset = 0, $count = FALSE) {
        if ($count === TRUE) {
            $query = $this->db->select()
                    ->from('lienhe')
                    ->get();
            return $query->num_rows();
        } else {
            $query = $this->db->select()
                    ->from('lienhe')
                    ->limit($perpage, $offset)
                    ->get();
            return $query->result_array();
        }
    }

    function _get_service_by_sale_id($sale_id, $perpage = 0, $offset = 0, $count = FALSE) {
        if ($count === TRUE) {
            $query = $this->db->select('dichvu.id, dichvu.ten_website, dichvu.ngay_bat_dau, dichvu.ngay_ket_thuc, dang_dichvu.ten_dich_vu, khachhang.ten_khach_hang, khachhang.ten_cong_ty, khachhang.id as id_khach_hang')
                    ->from('dichvu')
                    ->join('khachhang', 'khachhang.id = dichvu.id_khach_hang')
                    ->join('dang_dichvu', 'dang_dichvu.id = dichvu.id_dang_dich_vu')
                    ->where('dichvu.id_sale', $sale_id)
                    ->get();
            return $query->num_rows();
        } else {
            $query = $this->db->select('dichvu.id, dichvu.ten_website,  dichvu.ngay_bat_dau, dichvu.ngay_ket_thuc, dang_dichvu.ten_dich_vu, khachhang.ten_khach_hang, khachhang.ten_cong_ty, khachhang.id as id_khach_hang')
                    ->from('dichvu')
                    ->join('khachhang', 'khachhang.id = dichvu.id_khach_hang')
                    ->join('dang_dichvu', 'dang_dichvu.id = dichvu.id_dang_dich_vu')
                    ->where('dichvu.id_sale', $sale_id)
                    ->limit($perpage, $offset)
                    ->get();
            return $query->result_array();
        }
    }

    function _get_pay_infor_by_customer($cus_id) {
        $query = $this->db->select('dang_dichvu.ten_dich_vu, dichvu.id as dich_vu_id, dichvu.ten_website, dichvu.ngay_bat_dau, dichvu.ngay_ket_thuc, thanhtoan.id as thanh_toan_id, thanhtoan.so_tien, thanhtoan.tinh_trang')
                ->from('dichvu')
                ->join('dang_dichvu', 'dang_dichvu.id = dichvu.id_dang_dich_vu')
                ->join('thanhtoan', 'thanhtoan.id_dich_vu = dichvu.id')
                ->join('khachhang', 'khachhang.id = dichvu.id_khach_hang')
                ->where('khachhang.id', $cus_id)
                ->get();
        return $query->result_array();
    }

    function _get_pay_infor_by_service($id_service) {
        $query = $this->db->select('dot_thanh_toan.so_tien as tien, dot_thanh_toan.ngay_thanh_toan, dot_thanh_toan.ghi_chu, dot_thanh_toan.id_dot_thanh_toan, thanhtoan.*')
                ->from('thanhtoan')
                ->join('dot_thanh_toan', 'dot_thanh_toan.id_thanh_toan = thanhtoan.id')
                ->where('thanhtoan.id_dich_vu', $id_service)
                ->get();
        return $query->result_array();
    }

    function _get_customer_by_service($id_service) {
        $query = $this->db->select('khachhang.*')
                ->from('khachhang')
                ->join('dichvu', 'dichvu.id_khach_hang = khachhang.id')
                ->where('dichvu.id', $id_service)
                ->limit(1)
                ->get();
        return $query->row();
    }

    function _get_service_data_by_id($id_service) {
        $query = $this->db->select('dichvu.*, dang_dichvu.ten_dich_vu')
                ->from('dichvu')
                ->join('dang_dichvu', 'dang_dichvu.id = dichvu.id_dang_dich_vu')
                ->where('dichvu.id', $id_service)
                ->limit(1)
                ->get();
        return $query->row();
    }

    function _get_customer_id_by_pay($id) {
        $query = $this->db->select('dichvu.id_khach_hang')
                ->from('dichvu')
                ->join('thanhtoan', 'thanhtoan.id_dich_vu = dichvu.id')
                ->where('thanhtoan.id', $id)
                ->limit(1)
                ->get();
        return $query->row();
    }

    function _check_exist_customer($phone_number, $email) {
        $return = 1;
        $query = $this->db->select('khachhang.dien_thoai')
                ->from('khachhang')
                ->where('dien_thoai', $phone_number)
                ->get();
        if ($query->num_rows() > 0) {
            $return = 0;
        } else {
            $query1 = $this->db->select('khachhang.email')
                    ->from('khachhang')
                    ->where('email', $email)
                    ->get();
            if ($query1->num_rows() > 0) {
                $return = 0;
            }
        }

        if ($return == 0) {
            return FALSE;
        } else {
            return TRUE;
        }
    }

    function _check_exist_customer_edit($id, $phone_number, $email) {
        $return = 1;
        $query = $this->db->select('khachhang.dien_thoai')
                ->from('khachhang')
                ->where('dien_thoai', $phone_number)
                ->where('id <>', $id)
                ->get();
        if ($query->num_rows() > 0) {
            $return = 0;
        } else {
            $query1 = $this->db->select('khachhang.email')
                    ->from('khachhang')
                    ->where('email', $email)
                    ->where('id <>', $id)
                    ->get();
            if ($query1->num_rows() > 0) {
                $return = 0;
            }
        }

        if ($return == 0) {
            return FALSE;
        } else {
            return TRUE;
        }
    }

    function _check_exist_contact($phone_number) {
        $return = 1;
        $query = $this->db->select('lienhe.dien_thoai')
                ->from('lienhe')
                ->where('dien_thoai', $phone_number)
                ->get();
        if ($query->num_rows() > 0) {
            $return = 0;
        }

        return $return;
    }

    function _check_exist_contact_edit($id, $phone_number) {
        $return = 1;
        $query = $this->db->select('lienhe.dien_thoai')
                ->from('lienhe')
                ->where('dien_thoai', $phone_number)
                ->where('lienhe.id <>', $id)
                ->get();
        if ($query->num_rows() > 0) {
            $return = 0;
        }

        return $return;
    }

    function _get_service_type($perpage = 0, $offset = 0, $count = FALSE) {
        if ($count === TRUE) {
            $query = $this->db->select()
                    ->from('dang_dichvu')
                    ->get();
            return $query->num_rows();
        } else {
            $query = $this->db->select()
                    ->from('dang_dichvu')
                    ->limit($perpage, $offset)
                    ->get();
            return $query->result_array();
        }
    }

    function _search_data($text) {
        $query = $this->db->select()
                ->from('khachhang')
                ->like('ten_cong_ty', $text)
                ->get();
        return $query->result_array();
    }

    function _get_pay_infor($service_id) {
        $query = $this->db->select()
                ->from('thanhtoan')
                ->where('id_dich_vu', $service_id)
                ->limit(1)
                ->get();
        return $query->row();
    }

    function _get_pay_period_money($pay_id) {
        $query = $this->db->select_sum('so_tien')
                ->from('dot_thanh_toan')
                ->where('id_thanh_toan', $pay_id)
                ->get();
        return $query->result_array();
    }

    function _get_pay_not_done($sale_id, $is_admin = FALSE) {
        if ($is_admin === FALSE) {
            $query = $this->db->select('khachhang.*, thanhtoan.tinh_trang, thanhtoan.so_tien, dang_dichvu.ten_dich_vu, dichvu.ten_website, dichvu.ngay_ket_thuc, dichvu.id As dich_vu_id')
                    ->from('dichvu')
                    ->join('dang_dichvu', 'dichvu.id_dang_dich_vu = dang_dichvu.id')
                    ->join('thanhtoan', 'dichvu.id = thanhtoan.id_dich_vu')
                    ->join('khachhang', 'dichvu.id_khach_hang = khachhang.id')
                    ->join('users', 'khachhang.sale_id = users.id')
                    ->where('thanhtoan.tinh_trang', '0')
                    ->where('users.id', $sale_id)
                    ->order_by('dichvu.id', 'ASC')
                    ->get();
            return $query->result_array();
        } else {
            $query = $this->db->select('khachhang.*, thanhtoan.tinh_trang, thanhtoan.so_tien, dang_dichvu.ten_dich_vu, dichvu.ten_website, dichvu.ngay_ket_thuc, dichvu.id As dich_vu_id')
                    ->from('dichvu')
                    ->join('dang_dichvu', 'dichvu.id_dang_dich_vu = dang_dichvu.id')
                    ->join('thanhtoan', 'dichvu.id = thanhtoan.id_dich_vu')
                    ->join('khachhang', 'dichvu.id_khach_hang = khachhang.id')
                    ->join('users', 'khachhang.sale_id = users.id')
                    ->where('thanhtoan.tinh_trang', '0')
                    ->order_by('dichvu.id', 'ASC')
                    ->get();
            return $query->result_array();
        }
    }

}